/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.unesp.rc.locadora.dao;

import br.unesp.rc.locadora.beans.Fisica;
import br.unesp.rc.locadora.connection.FabricaConexao;
import br.unesp.rc.locadora.utils.DateManager;
import br.unesp.rc.locadora.utils.MySQLUtil;
import com.sun.rowset.CachedRowSetImpl;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.sql.RowSetMetaData;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetMetaDataImpl;
import javax.sql.rowset.spi.SyncProviderException;
import javax.sql.rowset.spi.SyncResolver;

/**
 *
 * @author aluno
 */
public class FisicaDAOImpl implements FisicaDAO {

    public FisicaDAOImpl() {
    }
    
    @Override
    public void print(Fisica f){
        System.out.println("NOME................: " + f.getNome());
        
        String date = DateManager.formatte(f.getDataNascimento());
        System.out.println("DATA DE NASCIMENTO..: " + date);
        
        System.out.println("CPF.................: " + f.getCpf());
        System.out.println("TELEFONE............: " + f.getContato().getTelefone());
        System.out.println("E-MAIL..............: " + f.getContato().getEmail());
        System.out.println("USUARIO.............: " + f.getAcesso().getUsuario());
        System.out.println("SENHA...............: " + f.getAcesso().getSenha());
        System.out.println("RUA.................: " + f.getEndereco().getRua());
        System.out.println("NUMERO..............: " + f.getEndereco().getNumero());
        System.out.println("BAIRRO..............: " + f.getEndereco().getBairro());
        System.out.println("CEP.................: " + f.getEndereco().getCep());
        System.out.println("CIDADE..............: " + f.getEndereco().getCidade());
        System.out.println("ESTADO..............: " + f.getEndereco().getEstado());
    }
    
    @Override
    public boolean cadastrar(Fisica f){
        boolean saved = false;
        PreparedStatement pstm = null;
        ResultSet res = null;
        long idUsuario = -1;

        Connection con = FabricaConexao.getConexao();
        if (con != null) {
            try {
                con.setAutoCommit(false);
                pstm = con.prepareStatement(MySQLUtil.INS_PESSOA, PreparedStatement.RETURN_GENERATED_KEYS);
                pstm.setString(1, f.getNome());
                pstm.setDate(2, new java.sql.Date(f.getDataNascimento().getTime()));
                pstm.executeUpdate();
                res = pstm.getGeneratedKeys();
                if (res.next()){
                    idUsuario = res.getLong(1);
                }
                System.out.println("ID: " + idUsuario);                
                
                pstm = con.prepareStatement(MySQLUtil.INS_FISICA);
                pstm.setLong(1, f.getCpf());
                pstm.setLong(2, idUsuario);
                pstm.executeUpdate();
                
                pstm = con.prepareStatement(MySQLUtil.INS_ENDERECO);
                pstm.setString(1, f.getEndereco().getRua());
                pstm.setInt(2, f.getEndereco().getNumero());
                pstm.setString(3, f.getEndereco().getBairro());
                pstm.setString(4, f.getEndereco().getCep());
                pstm.setString(5, f.getEndereco().getCidade());
                pstm.setString(6, f.getEndereco().getEstado());
                pstm.setLong(7, idUsuario);
                pstm.executeUpdate();
                
                pstm = con.prepareStatement(MySQLUtil.INS_CONTATO);
                pstm.setString(1, f.getContato().getTelefone());
                pstm.setString(2, f.getContato().getEmail());
                pstm.setLong(3, idUsuario);
                pstm.executeUpdate();

                pstm = con.prepareStatement(MySQLUtil.INS_ACESSO);
                pstm.setString(1, f.getAcesso().getUsuario());
                pstm.setString(2, f.getAcesso().getSenha());
                pstm.setLong(3, idUsuario);
                pstm.executeUpdate();
                
                con.commit();
                saved = true;
            } catch (SQLException ex) {
                System.out.println("Problemas ao inserir: " + ex.getMessage());
            } finally{
                FabricaConexao.fechar(con);
            }
        }

        return saved;
    }
    
    @Override
    public boolean cadastrarCache(Fisica f){
        boolean saved = false;
        long idUsuario = -1,id = -1;
        
        Connection con = FabricaConexao.getConexao();
        if (con != null) {
            try{
                con.setAutoCommit(false);
                
                CachedRowSet crs = new CachedRowSetImpl();
                crs.setCommand(MySQLUtil.INS_PESSOA);
                crs.setString(1, f.getNome());
                crs.setDate(2, new java.sql.Date(f.getDataNascimento().getTime()));
                crs.execute(con);
                
                crs.setCommand(MySQLUtil.GET_LAST_INSERT_ID);
                crs.execute(con);
                if (crs.next()){
                    idUsuario = crs.getLong(1);
                }
                System.out.println("ID: " + idUsuario);                
                
                crs.setCommand(MySQLUtil.INS_FISICA);
                crs.setLong(1, f.getCpf());
                crs.setLong(2, idUsuario);
                crs.execute(con);
                
                crs.setCommand(MySQLUtil.INS_ENDERECO);
                crs.setString(1, f.getEndereco().getRua());
                crs.setInt(2, f.getEndereco().getNumero());
                crs.setString(3, f.getEndereco().getBairro());
                crs.setString(4, f.getEndereco().getCep());
                crs.setString(5, f.getEndereco().getCidade());
                crs.setString(6, f.getEndereco().getEstado());
                crs.setLong(7, idUsuario);
                crs.execute(con);
                
                crs.setCommand(MySQLUtil.INS_CONTATO);
                crs.setString(1, f.getContato().getTelefone());
                crs.setString(2, f.getContato().getEmail());
                crs.setLong(3, idUsuario);
                crs.execute(con);

                crs.setCommand(MySQLUtil.INS_ACESSO);
                crs.setString(1, f.getAcesso().getUsuario());
                crs.setString(2, f.getAcesso().getSenha());
                crs.setLong(3, idUsuario);
                crs.execute(con);
                
                con.commit(); 
                saved = true;
            
            }catch(SQLException ex){
                System.out.println("Problemas: " + ex.getMessage());
                ex.printStackTrace();
            } finally{
                FabricaConexao.fechar(con);
            }
        }
        
        return saved;
    }
    
    private void resolve(SyncProviderException spe, CachedRowSet crs) throws SQLException{
      SyncResolver resolver = spe.getSyncResolver();

      Object crsValue; // value in the RowSet object
      Object resolverValue; // value in the SyncResolver object
      Object resolvedValue; // value to be persisted

      while (resolver.nextConflict()) {

        if (resolver.getStatus() == SyncResolver.INSERT_ROW_CONFLICT) {
          int row = resolver.getRow();
          crs.absolute(row);

          int colCount = crs.getMetaData().getColumnCount();
          for (int j = 1; j <= colCount; j++) {
            if (resolver.getConflictValue(j) != null) {
              crsValue = crs.getObject(j);
              resolverValue = resolver.getConflictValue(j);

              // Compare crsValue and resolverValue to determine
              // which should be the resolved value (the value to persist)
              //
              // This example choses the value in the RowSet object,
              // crsValue, to persist.,

              resolvedValue = crsValue;

              resolver.setResolvedValue(j, resolvedValue);
            }
          }
        }
      }
    }
    
    @Override
    public boolean cadastrarProc(Fisica f){
        boolean saved = false;
        PreparedStatement pstm = null;

        Connection con = FabricaConexao.getConexao();
        if (con != null) {
            try {
                con.setAutoCommit(false);
                pstm = con.prepareStatement(MySQLUtil.PROC_INS_PESSOAFISICA, PreparedStatement.RETURN_GENERATED_KEYS);
                pstm.setString(1, f.getNome());
                pstm.setDate(2, new java.sql.Date(f.getDataNascimento().getTime()));
                pstm.setLong(3, f.getCpf());
                pstm.setString(4, f.getEndereco().getRua());
                pstm.setInt(5, f.getEndereco().getNumero());
                pstm.setString(6, f.getEndereco().getBairro());
                pstm.setString(7, f.getEndereco().getCep());
                pstm.setString(8, f.getEndereco().getCidade());
                pstm.setString(9, f.getEndereco().getEstado());
                pstm.setString(10, f.getAcesso().getUsuario());
                pstm.setString(11, f.getAcesso().getSenha());
                pstm.setString(12, f.getContato().getTelefone());
                pstm.setString(13, f.getContato().getEmail());
                pstm.executeUpdate();               
                
                System.out.println("OK");
                
                con.commit();
                saved = true;
            } catch (SQLException ex) {
                System.out.println("Problemas ao inserir: " + ex.getMessage());
            } finally{
                FabricaConexao.fechar(con);
            }
        }

        return saved;
    }
    
    public List<String> getUsuarios(){
        PreparedStatement pstm = null;
        ResultSet res = null;
        List<String> retorno = new ArrayList<String>();
        
        Connection con = FabricaConexao.getConexao();
        try {
                con.setAutoCommit(false);
                pstm = con.prepareStatement(MySQLUtil.SEL_USUARIO_ACESSO);
                res = pstm.executeQuery();
                
                while(res.next()){
                    retorno.add(res.getString(1));
                }
                
                System.out.println("OK");
                
                con.commit();
        } catch (SQLException ex) {
                System.out.println("Problemas ao inserir: " + ex.getMessage());
        } finally{
                FabricaConexao.fechar(con);
        }
        
        return retorno;
    }
}
